using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace PropertyExpression.ControllableQuery.Test
{
    public static class DB
    {
        private static readonly ConcurrentDictionary<string, string> insertDictionary = new ConcurrentDictionary<string, string>();

        public static QFragment Insert<T>(string tableName, T param)
        {
            return writer => {
                string value;
                if (!insertDictionary.TryGetValue(tableName, out value))
                {
                    var list = GetColumns(tableName).ToList();
                    value = "INSERT INTO " + tableName + " ("
                        + string.Join(", ", list) + ") VALUES ("
                        + string.Join(", ", list.Select(_ => "@" + _)) + ")";
                    insertDictionary[tableName] = value;
                }
                writer.WriteLiteral(value);
                writer.AddParams(param);
            };
        }

        private static readonly ConcurrentDictionary<string, string> updateDictionary = new ConcurrentDictionary<string, string>();

        public static QFragment Update<T>(string tableName, T param)
        {
            return writer => {
                string value;
                if (!updateDictionary.TryGetValue(tableName, out value))
                {
                    var primaryKey = GetPrimaryKey(tableName).ToList();
                    value = "UPDATE " + tableName + " SET "
                        + string.Join(", ", GetColumns(tableName).ToList().Except(primaryKey)
                            .Select(_ => _ + " = @" + _))
                        + " WHERE "
                        + string.Join(" AND ", primaryKey.Select(_ => _ + " = @" + _));
                    updateDictionary[tableName] = value;
                }
                writer.WriteLiteral(value);
                writer.AddParams(param);
            };
        }

        private static readonly ConcurrentDictionary<string, string> deleteDictionary = new ConcurrentDictionary<string, string>();

        public static QFragment Delete<T>(string tableName, T param)
        {
            return writer => {
                string value;
                if (!deleteDictionary.TryGetValue(tableName, out value))
                {
                    value = "DELETE " + tableName + " WHERE "
                        + string.Join(" AND ", GetPrimaryKey(tableName).ToList().Select(_ => _ + " = @" + _));
                    deleteDictionary[tableName] = value;
                }
                writer.WriteLiteral(value);
                writer.AddParams(param);
            };
        }

        private static IEnumerable<string> GetColumns(string tableName)
        {
            using (var connection = QueryTest.DbConnectionFunc())
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
SELECT  columns.name
FROM    sys.columns
WHERE   columns.object_id = OBJECT_ID(@tableName)";
                    command.Parameters.Add(new SqlParameter {
                        SqlDbType = SqlDbType.NVarChar,
                        Size = -1,
                        ParameterName = "@tableName",
                        Value = tableName
                    });
                    using (var reader = command.ExecuteReader())
                        while (reader.Read())
                            yield return (string) reader[0];
                }
            }
        }

        private static IEnumerable<string> GetPrimaryKey(string tableName)
        {
            using (var connection = QueryTest.DbConnectionFunc())
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = @"
SELECT column_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1
AND table_name = @tableName";
                    command.Parameters.Add(new SqlParameter {
                        SqlDbType = SqlDbType.NVarChar,
                        Size = -1,
                        ParameterName = "@tableName",
                        Value = tableName
                    });
                    using (var reader = command.ExecuteReader())
                        while (reader.Read())
                            yield return (string)reader[0];
                }
            }
        }
    }
}